# -*- coding: utf-8 -*-
'''
@author: José Ribeiro nº5316
'''
import sqlite3
import Dados

#class Base_Dados
#responsavel por Criar a Base dados
#inclui metodos para criar tabelas,
#para apagar as tabelas e
#inserir dados nas tabelas
class Base_Dados:
    def __init__(self, nome_BD):
        self.nome_BD = nome_BD
        pass
    
    #Metodo para criar as tabelas
    #recebe como paramentro o nome da Base dados
    def criar_tabelas(self, nome_BD):
        '''
        Criar as tabelas da Base Dados
        '''
        conn = sqlite3.connect(nome_BD)
        cursor = conn.cursor()
        
        # criar a tabela com o tipo de Estabelecimentos
        cursor.execute("""CREATE TABLE IF NOT EXISTS tipos_estabelecimentos
                        (codTipoEstab INT, 
                        nameTipoEstab TEXT PRIMARY KEY)""")
        # criar a tabela docentes
        cursor.execute("""CREATE TABLE IF NOT EXISTS docentes
                        (nomeDocente TEXT PRIMARY KEY,  
                        codigoDocente INTEGER UNIQUE,
                        ano INTEGER )""")
        #Cria a tabela graus
        cursor.execute("""CREATE TABLE IF NOT EXISTS graus 
                      (designacao TEXT PRIMARY KEY)
                       """)
        #Cria a tabela cursos
        cursor.execute("""CREATE TABLE IF NOT EXISTS cursos
                          (designacao TEXT PRIMARY KEY)
                       """)
        #Cria a tabela categorias
        cursor.execute("""CREATE TABLE IF NOT EXISTS categorias
                          (designacao TEXT PRIMARY KEY)
                       """)
        #Cria a tabela regiems
        cursor.execute("""CREATE TABLE IF NOT EXISTS regimes
                          (designacao TEXT PRIMARY KEY)
                       """)
        #Cria a tabela estabelecimentos
        cursor.execute("""CREATE TABLE IF NOT EXISTS estabelecimentos
                (codEstab INTEGER , 
                nameEstab TEXT PRIMARY KEY,
                codTipoEstab REFERENCES tipos_estabelecimento (codTipoEstab))""")
        #Cria a tabela registo_docentes
        cursor.execute("""CREATE TABLE IF NOT EXISTS registo_docentes
                      (id INTEGER PRIMARY KEY AUTOINCREMENT, 
                       ano INTEGER,
                       docente REFERENCES docentes (nomeDocente),
                       estabelecimento REFERENCES estabelecimentos (nameEstab),
                       categoria REFERENCES categorias (designacao),
                       grau REFERENCES graus (designacao),
                       regime REFERENCES regimes (designacao),
                       tipo_estabelecimento REFERENCES 
                       tipos_estabelecimento (nameTipoEstab))
                   """)
        #Cria a tabela registo_curso
        cursor.execute("""CREATE TABLE IF NOT EXISTS registo_curso
                      (id INTEGER PRIMARY KEY AUTOINCREMENT, 
                       ano INTEGER,
                       docente REFERENCES docentes (nomeDocente),
                       grau REFERENCES graus (designacao),
                       curso REFERENCES cursos (designacao))
                   """)
        cursor.close()
        pass
    
    #Elimina as Tabelas da Base Dados
    #"tipos_estabelecimentos", "docentes", "graus"
    #"cursos", "categorias", "regimes", "estabelecimentos"
    #"registo_docentes", "registo_curso"
    #Recebe como parametro o nome da Base Dados (none_BD)
    def apagar_Tabelas(self, none_BD):
        conn = sqlite3.connect(none_BD)
        conn.execute('''DROP TABLE IF EXISTS tipos_estabelecimentos''')
        conn.execute('''DROP TABLE IF EXISTS docentes''')
        conn.execute('''DROP TABLE IF EXISTS graus''')
        conn.execute('''DROP TABLE IF EXISTS cursos''')
        conn.execute('''DROP TABLE IF EXISTS categorias''')
        conn.execute('''DROP TABLE IF EXISTS regimes''')
        conn.execute('''DROP TABLE IF EXISTS estabelecimentos''')
        conn.execute('''DROP TABLE IF EXISTS registo_docentes''')
        conn.execute('''DROP TABLE IF EXISTS registo_curso''')
        conn.close()
    
    #metodo para preencher a tabela tipo de estabelecimento
    #recebe como parametro o nome da Base dados e o ano
    def preencher_tabelas_tipos_estabelecimento(self, nome_BD, ano):
        '''
        preenchimento da tabela tipos_estabelecimento
        '''
        conn = sqlite3.connect(nome_BD)
        cursor = conn.cursor()
        print "ANO: 200{0}".format(ano)
        d_tipos_estabelecimento = Dados.Dados1(ano).obter_codigos_tipo_estabelecimento(ano)
    
        for tipo, codR in d_tipos_estabelecimento.iteritems():
            try:
                cmd = """insert into tipos_estabelecimentos (codTipoEstab, nameTipoEstab
                        ) values ('{0}', '{1}')""".format(codR, tipo)
                cursor.execute(cmd)
            except:
                pass
            pass
        conn.commit()
        cursor.close()
        pass
    
    #metodo para preencher a tabela estabelecimento
    #recebe como parametro o nome da Base dados e o ano
    def prencher_tabela_estabelecimento(self, nome_BD, ano):
        '''
        preenchimento da tabela estabelecimento
        '''
        conn = sqlite3.connect(nome_BD)
        cursor = conn.cursor()
        print "ANO: 200{0}".format(ano)
        d_codigo_tipo_estabelecimento = Dados.Dados1(ano).obter_codigos_tipo_estabelecimento(ano)
        for nome_T_Est, codR in d_codigo_tipo_estabelecimento.iteritems():
            d_estabelecimentos = Dados.Dados1(ano).obter_codigos_estabelecimentos(ano, codR)
            #contador = 0
            for nome_Est, codP in d_estabelecimentos.iteritems():
                try:
                    cmd = """insert into estabelecimentos (codEstab, nameEstab, codTipoEstab) 
                        values ('{0}', '{1}', '{2}')""".format(codP, nome_Est, codR)
                    cursor.execute(cmd)
                except:
                    pass 
                pass
            pass
        conn.commit()
        cursor.close()
        pass

    #metodo para preencher a tabela docentes
    #recebe como parametro a lista com informaçao dos docentes
    def preencher_docentes(self, lista, conn, cursor):
        '''
        preenchimento da docentes
        '''
        C_CODIGO = 0
        C_DOCENTE = 1
        C_ANO = 6
        # uma linha corresponde a uma entrada 
        for linha in lista:
            for x in linha:
                cmd = """insert into docentes (nomeDocente, codigoDocente, ano) values ('{0}', '{1}', {2})""".\
                    format(x[C_DOCENTE], x[C_CODIGO], x[C_ANO])
                print cmd
                try:
                    cursor.execute(cmd)
                    conn.commit()
                except:
                    continue
                pass
            pass       
        pass
    
    #metodo para preencher a tabela registo_curso
    #recebe como parametro a lista com informaçao dos docentes
    def preencher_Tabela_registo_curso(self, lista, conn, cursor):
        """
        preenchimento da tabela registo_curso
        """
        C_CODIGO = 0
        C_DOCENTE = 1
        C_GRAU = 2
        C_CURSO = 3
        C_ANO = 6
     
        # uma linha corresponde a uma entrada 
        for linha in lista:
            for x in linha:
                # selecciona a referência do grau
                cmd = """SELECT designacao FROM graus WHERE designacao = '{0}'""".\
                    format(x[C_GRAU])
                print cmd
                try:
                    cursor.execute(cmd)
                    r = cursor.fetchone()
                    grau = r[0]
                except:
                    continue
     
                # selecciona a referência do curso
                cmd = """SELECT designacao FROM cursos WHERE designacao = '{0}'""".\
                    format(x[C_CURSO])
                print cmd
                try:
                    cursor.execute(cmd)
                    r = cursor.fetchone()
                    curso = r[0]
                except:
                    continue
     
                # selecciona a referência do docente
                cmd = """SELECT nomeDocente FROM docentes where nomeDocente = '{0}'""".\
                    format(x[C_DOCENTE])
                print cmd
                try:
                    cursor.execute(cmd)
                    r = cursor.fetchone()
                    docente = r[0]
                except:
                    continue
     
                # insere na base de dados
                cmd = """INSERT INTO registo_curso 
                         (ano, docente, grau, curso)
                         VALUES 
                         ('{0}','{1}','{2}','{3}') """.\
                    format(x[C_ANO], 
                           docente.encode('utf-8'), 
                           grau.encode('utf-8'), 
                           curso.encode('utf-8'))
                print cmd
                try:
                    cursor.execute(cmd)
                except:
                    continue
                pass
            pass
        conn.commit()
        pass
        
    #metodo para preencher a tabela registo_docentes
    #recebe como parametro a lista com informaçao dos docentes
    def preencher_Tabela_Registo_docencte(self, lista, conn, cursor):
        """
        preenchimento da tabela registo_docentes
        """
        C_CODIGO = 0
        C_DOCENTE = 1
        C_GRAU = 2
        C_CURSO = 3
        C_CATEGORIA = 4
        C_REGIME = 5
        C_ANO = 6
        C_TIPO_ENSINO = 7
        C_ESTABELECIMENTO = 8
    
        # uma linha corresponde a uma entrada 
        for linha in lista:
            for x in linha:
                # selecciona a referência do docente
                cmd = """SELECT nomeDocente FROM docentes WHERE nomeDocente = '{0}'""".\
                    format(x[C_DOCENTE])
                print cmd
                try:
                    cursor.execute(cmd)
                    r = cursor.fetchone()
                    docente = r[0].encode('utf-8')
                except:
                    continue
                
                cmd = """SELECT designacao FROM graus WHERE designacao = '{0}'""".\
                    format(x[C_GRAU])
                print cmd
                try:
                    cursor.execute(cmd)
                    r = cursor.fetchone()
                    grau = r[0].encode('utf-8')
                except:
                    continue
    
                # selecciona a referência do estabelecimento
                cmd = """SELECT nameEstab FROM estabelecimentos 
                         WHERE nameEstab = '{0}'""".\
                    format(x[C_ESTABELECIMENTO])
                try:
                    cursor.execute(cmd)
                    r = cursor.fetchone()
                    estabelecimento = r[0].encode('utf-8')
                except:
                    continue
    
                # selecciona a referência da categoria
                cmd = """SELECT designacao FROM categorias WHERE designacao = '{0}'""".\
                    format(x[C_CATEGORIA])
                try:
                    cursor.execute(cmd)
                    r = cursor.fetchone()
                    categoria = r[0].encode('utf-8')
                except:
                    continue
    
                # selecciona a referência do regime
                cmd = """SELECT designacao FROM regimes WHERE designacao = '{0}'""".\
                    format(x[C_REGIME])
                print cmd
                try:
                    cursor.execute(cmd)
                    r = cursor.fetchone()
                    regime = r[0].encode('utf-8')
                except:
                    continue
    
                # selecciona a referência do tipo de estabelecimento
                cmd = """SELECT nameTipoEstab FROM tipos_estabelecimentos 
                         WHERE nameTipoEstab = '{0}'""".\
                    format(x[C_TIPO_ENSINO])
                print cmd
                try:
                    cursor.execute(cmd)
                    r = cursor.fetchone()
                    tipo_estabelecimento = r[0].encode('utf-8')
                except:
                    continue
    
                # insere na base de dados
                cmd = """INSERT INTO registo_docentes 
                         (ano, 
                          docente, 
                          estabelecimento, 
                          categoria,
                          grau,
                          regime, 
                          tipo_estabelecimento)
                         VALUES 
                         ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}') """.\
                         format(x[C_ANO], docente, estabelecimento, categoria, 
                                 grau, regime, tipo_estabelecimento)    
                try:
                    cursor.execute(cmd)
                except:
                    continue
                pass
            pass
        conn.commit()
        pass
    
    #metodo para preencher as tabelas graus, cursos, categorias,
    #regimes
    def preencher_geral(self, lista, conn, cursor, tipo):
        """
        prenchimento das tabelas graus, cursos, categorias,
                regimes
        """
        d_informacao = {}
        d_informacao['graus'] = 2
        d_informacao['cursos'] = 3
        d_informacao['categorias'] = 4
        d_informacao['regimes'] = 5
        d_informacao['estabelecimentos'] = 8
     
        for x in lista:
            cmd = """insert into {0} (designacao) values ('{1}')""".\
                format(tipo, x)
     
            try:
                cursor.execute(cmd)
     
            except:
                print tipo, x
                pass
            pass
        conn.commit()
        pass
    
    def organizar_conjuntos_unicos(self, lista_entrada, tipo):
        d_informacao = {}
        d_informacao['graus'] = 2
        d_informacao['cursos'] = 3
        d_informacao['categorias'] = 4
        d_informacao['regimes'] = 5
        d_informacao['estabelecimentos'] = 8
    
        conjunto_unico = set()
        lista = []
        for ld in lista_entrada:
            for x in ld:
                conjunto_unico.add(x[d_informacao[tipo]])
                pass
            pass
        
        for y in conjunto_unico:
            lista.append(y)
            pass
    
        return lista
    
    # Método para utilizado para preencher todoas as tabelas
    # da Base de Dados, que recebe o nome da base Dados (nome_BD) e
    # o ano a que se referem os dados (ano)   
    def prencher_Todas_Tabelas_BD(self, nome_BD, ano):
        '''    
        codR -> código do tipo de estabelecimento        
        codP -> código do estabelecimento 
        '''
        # preenche a tabela tipos de estabelecimentos, enviando como parametro
        # o nome da base de dados e o ano a que se refere os dados
        self.preencher_tabelas_tipos_estabelecimento(nome_BD, ano)
        #preenche a tabela estabelecimentos, enviando como parametro
        # o nome da base de dados e o ano a que se refere os dados 
        self.prencher_tabela_estabelecimento(nome_BD, ano)
        
        conn = sqlite3.connect(nome_BD)
        cursor = conn.cursor()
        print "ANO: 200{0}".format(ano)
        lista = []
        d_codigo_tipo_estabelecimento = Dados.Dados1(ano).obter_codigos_tipo_estabelecimento(ano)
        
        for nome_T_Est, codR in d_codigo_tipo_estabelecimento.iteritems():
            d_estabelecimentos = Dados.Dados1(ano).obter_codigos_estabelecimentos(ano, codR)
           
            print nome_T_Est, codR
            for nome_Est, codP in d_estabelecimentos.iteritems():
                
                print nome_Est, codP
                ld = Dados.Dados1(ano).obter_informacao_docentes(ano, nome_T_Est, codR, nome_Est, codP)
                lista.append(ld)
                pass
            pass
        
        
        lista_unica = self.organizar_conjuntos_unicos(lista, 'graus')
        # preencher a tabela graus, enviando como parametro, a lista
        # com todos os graus, o conect a base dados e o tipo de tabela
        # que é neste caso a tabela graus
        self.preencher_geral(lista_unica, conn, cursor, 'graus')
        
        lista_unica = self.organizar_conjuntos_unicos(lista, 'cursos')
        # preencher a tabela cursos, enviando como parametro, a lista
        # com todos os cursos, o conect a base dados e o tipo de tabela
        # que é neste caso a tabela cursos
        self.preencher_geral(lista_unica, conn, cursor, 'cursos')
        
        lista_unica = self.organizar_conjuntos_unicos(lista, 'regimes')
        # preencher a tabela regimes, enviando como parametro, a lista
        # com todos os regimes, o conect a base dados e o tipo de tabela
        # que é neste caso a tabela regimes
        self.preencher_geral(lista_unica, conn, cursor, 'regimes')
        
        lista_unica = self.organizar_conjuntos_unicos(lista, 'categorias')
        # preencher a tabela categorias, enviando como parametro, a lista
        # com todos as categorias, o conect a base dados e o tipo de tabela
        # que é neste caso a tabela categorias
        self.preencher_geral(lista_unica, conn, cursor, 'categorias')
        
        #preencher a tabela docentes, enviando como parametro, a lista
        # com alguma informaçao dos docentes 
        # (ano, nome do Tipo de Estabelecimento, o codR, o nome estabelecimento
        # e o codP) e o conect a base dados
        self.preencher_docentes(lista, conn, cursor)
        
        # preencher a tabela registo dos cursos, enviando como parametro, a lista
        # com alguma informaçao dos docentes 
        # (ano, nome do Tipo de Estabelecimento, o codR, o nome estabelecimento
        # e o codP) e o conect a base dados
        self.preencher_Tabela_registo_curso(lista, conn, cursor)
        #cursor.close()
        
        # preencher a tabela resgisto dos docentes docentes, enviando como parametro, a lista
        # com alguma informaçao dos docentes 
        # (ano, nome do Tipo de Estabelecimento, o codR, o nome estabelecimento
        # e o codP) e o conect a base dados
        self.preencher_Tabela_Registo_docencte(lista, conn, cursor)
        cursor.close()
        pass
    pass
